﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace TooltracePC
{
    class dbTool : clsDatabase
    {
        private static int nToolCount = 0;



        public int getToolCount()
        {
            if (nToolCount > 0)
            {
                return nToolCount;
            }
            setToolCount();
            return nToolCount;
        }

        private void setToolCount()
        {
            string strSql = @"select count(*) AS c from Tool";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            nToolCount = (int)dtTool.Rows[0]["c"];
        }


        //get All
        public DataTable getAll()
        {
            string strSql = @"select * from Tool";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);

            return dtTool;
        }

        //get
        public DataTable getAccLableID(string strName)
        {
            string strSql = @"select * from Tool where LableID = '" + strName + "'";
            return SelectToDataTable(strSql);
        }


        //get by name
        public DataTable getByName(string strName)
        {
            string strSql = @"select * from Tool where LableName Like '%"+strName+ "%' or LableNo Like '%"+ strName + "%'";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }

        //get by ID

        public DataTable getByID(int nID)
        {

            string strSql = @"select * from Tool where ID = " + nID;
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }



        //inster
        public int insertDb(string LableID, string LableName, string LableNo)
        {
            string strSQL;

            if (string.IsNullOrEmpty(LableID) || string.IsNullOrEmpty(LableName) || string.IsNullOrEmpty(LableNo))
            {
                MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            DataTable _dt = new DataTable();
            _dt = getAccLableID(LableID);
            if (_dt.Rows.Count > 0)
            {
                MessageBox.Show("此标签已存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
               
            }

            strSQL = "insert into Tool(LableID, LableName, LableNo) values('"+ LableID + "', '"+ LableName + "', '"+ LableNo + "')";
            execNonSQL(strSQL);
            return 1;
        }


        //update
        public int updateDb(int _id, string LableID, string LableName, string LableNo)
        {
            string strSQL;

            if (string.IsNullOrEmpty(LableID) || string.IsNullOrEmpty(LableName) || string.IsNullOrEmpty(LableNo))
            {
                MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            DataTable _dt = new DataTable();
            _dt = getAccLableID(LableID);
            if (_dt.Rows.Count > 0)
            {
                if (int.Parse(_dt.Rows[0]["ID"].ToString()) != _id)
                {
                    MessageBox.Show("此标签已存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return 0;
                }
            }

            strSQL = "update Tool set LableID = '"+ LableID + "', LableName = '"+ LableName + "', LableNo = '"+ LableNo + "' where ID = " + _id;
            execNonSQL(strSQL);
            return 1;
        }

        //delete
        public int deleteDb(int[] _id)
        {
            int nResutl = 0;
            string strSQL = "delete from Tool where id in (";
            if (_id == null || _id.Length == 0)
            {
                return nResutl;
            }

            for(int i = 0; i < _id.Length; i++)
            {
                if(i + 1 == _id.Length)
                {
                    strSQL += _id[i].ToString();
                }
                else
                {
                    strSQL += _id[i].ToString() + ",";
                }
                
            }

            strSQL += ")";

            execNonSQL(strSQL);
           
            nResutl = 1;
            return nResutl;
        }


    }
}
